package com.example.demo3;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.commons.lang3.StringUtils;

import java.util.Iterator;
import java.util.Map;

/**
 * excel 工具类
 *
 * @author QuiFar
 * @version V1.0
 **/
public class ExcelUtil {
    /**
     * 设置模板中指定占位符的内容。
     *
     * @param sheet 工作表
     * @param para  模板中的占位符与值的健值对。
     */
    public static void setCellValue(Sheet sheet, Map<String, String> para) {
        Iterator<Row> rowIt = sheet.rowIterator();
        while (rowIt.hasNext()) {
            Row row = rowIt.next();
            //System.err.println("行高：" + row.getHeight());
            if (row == null) {
                continue;
            }

            Iterator<org.apache.poi.ss.usermodel.Cell> cellIt = row.cellIterator();
            while (cellIt.hasNext()) {
                org.apache.poi.ss.usermodel.Cell cell = cellIt.next();

                String cellValue = String.valueOf(getCellValue(cell));

                //String cellValue = cell.getStringCellValue();
                String value = null;
                if (cellValue != null && cellValue.startsWith("$")) {
                    String key = cellValue.substring(1);
                    // 替换值
                    value = para.get(key);
                    if (para.containsKey(key) && value == null) {
                        value = "";
                    }
                }

                // 为当前单元格赋值
                if (value != null) {
                    cell.setCellValue(value);
                }
            }
        }
    }


    /**
     * 单元格赋值
     *
     * @param row  行
     * @param data 数据（map类型）
     */
    public static void setCellValue(HSSFRow row, CellStyle style, Map<String, String> data) {
        Iterator<Cell> cells = row.cellIterator();
        int i = 0;
        for (String k : data.keySet()) {
//            Cell cell = cells.next();
//            Object value = data.get(k);
//
//            String bValue = String.valueOf(value);
//            cell.setCellValue(bValue);

            HSSFCell cell = row.createCell(i);
            String value = data.get(k);
            cell.setCellValue(value);

            i++;

            // 设置每个单元格边框
            setCellStyle(style, cell);
        }
    }

    /**
     * 单元格赋值(需要换行,空掉每行的前 X 个cell)
     *
     * @param row      行
     * @param spaceNum 当前行需要忽略的单元格数
     * @param data     数据（map类型）
     */
    public static void setCellValueChangeLine(HSSFRow row, int spaceNum, CellStyle style, Map<String, String> data) {
        Iterator<org.apache.poi.ss.usermodel.Cell> cells = row.cellIterator();
        // 跳过第一排
        org.apache.poi.ss.usermodel.Cell cell;
        for (int m = 0; m < spaceNum; m++) {
            cell = cells.next();
        }

        for (String k : data.keySet()) {
            cell = cells.next();
            Object value = data.get(k);
            String bValue = String.valueOf(value);
            cell.setCellValue(bValue);

            // 设置每个单元格边框
            setCellStyle(style, cell);
        }
    }

    /**
     * 获取单元格值
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {

        if (cell == null && StringUtils.isBlank(cell.getStringCellValue())) {
            return null;
        }

        int cellType = cell.getCellType();

        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return null;
        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cellType == Cell.CELL_TYPE_ERROR) {
            return cell.getErrorCellValue();
        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
            try {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            } catch (IllegalStateException e) {
                return cell.getRichStringCellValue();
            }
        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return cell.getNumericCellValue();
            }
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else {
            return null;
        }
    }


    /**
     * @param style
     * @param sheet
     */
    public static void setCellStyleA5(CellStyle style, Sheet sheet) {

        int i = 7;
        int totalLine = 10;
        for (int j = 0; j < totalLine; j++) {
            Row row = sheet.getRow(i + j);
            Iterator<Cell> cells = row.cellIterator();

            // 为0-12单元格设置样式
            for (int k = 0; k < 12; k++) {
                Cell cell = cells.next();
                // 设置每个单元格边框
                setCellStyle(style, cell);
            }

        }
    }


    /**
     * @param style
     * @param sheet
     */
    public static void setCellStyleA4(CellStyle style, Sheet sheet) {

        int intRowNum = 7;
        int totalLine = 25;
        int cellCount = 12;
        for (int j = 0; j < totalLine; j++) {
            Row row = sheet.createRow(intRowNum + j);


            // 为0-12单元格设置样式
            for (int k = 0; k < cellCount; k++) {
                Cell cell = row.createCell(k);
                // 设置每个单元格边框
                setCellStyle(style, cell);
            }

        }
    }

    public static void setCellStyle(CellStyle style, Cell cell) {

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);

        //设置上下左右四个边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        cell.setCellStyle(style);
    }
}
